﻿' ------------------------------------------------------------------------------
' The AlbumManager class maintains a dictionary of Album objects which are
' loaded from the Album table, and also contains the current Album. It provides
' a DataTable that contains rows from the Photo table that were retrieved using
' the sql query for the current Album.
' ------------------------------------------------------------------------------
' This file is part of the SQL Server 2008 Technology Demonstration. This
' application was designed and coded by Eric Weitzman, LandTime Inc, for
' Microsoft Corporation. Contact Ed Katibah at Microsoft about this demo.
' Copyright © 2008 Microsoft Corporation. All rights reserved.
' ------------------------------------------------------------------------------

Public Class Album

    Private m_rowguid As System.Guid
    Private m_name As String
    Private m_query As String

    Public Sub New(ByVal rowguid As System.Guid, ByVal name As String, ByVal query As String)
        m_rowguid = rowguid
        m_name = name
        m_query = query
    End Sub

    Public Sub New(ByVal name As String, ByVal query As String)
        m_rowguid = System.Guid.NewGuid
        m_name = name
        m_query = query
    End Sub

    Public Overrides Function ToString() As String
        Return m_name
    End Function

#Region " properties "
    Public Property AlbumName() As String
        Get
            Return m_name
        End Get
        Set(ByVal value As String)
            m_name = value
        End Set
    End Property
    Public ReadOnly Property RowGuid() As System.Guid
        Get
            Return m_rowguid
        End Get
    End Property
    Public Property Query() As String
        Get
            Return m_query
        End Get
        Set(ByVal value As String)
            m_query = value
        End Set
    End Property
#End Region

End Class

Public Class AlbumManager

    Private m_currentalbum As Album
    Private m_currentalbums As Dictionary(Of String, Album)
    Private m_photos As DataTable
    Private m_first_album As Album

    Private m_connection As Connection

    Public Event AlbumsChanged()
    Public Event CurrentAlbumChanged(ByVal currentalbum As Album)
    Public Event StartAlbumLoad()
    Public Event AlbumLoaded()

#Region " properties "
    Public Property CurrentAlbum() As Album
        Get
            Return m_currentalbum
        End Get
        Set(ByVal value As Album)
            If Not m_currentalbums.ContainsValue(value) Then AddAlbum(value)
            If m_currentalbum Is value Then Return
            m_currentalbum = value
            RaiseEvent CurrentAlbumChanged(m_currentalbum)
        End Set
    End Property
    Public ReadOnly Property Photos() As DataTable
        Get
            Return m_photos
        End Get
    End Property
    Public ReadOnly Property Albums() As Dictionary(Of String, Album)
        Get
            Return m_currentalbums
        End Get
    End Property
#End Region

    Public Function ContainsAlbumName(ByVal name As String) As Boolean
        For Each albumname As String In Albums.Keys
            If albumname.ToLower = name.ToLower Then Return True
        Next
        Return False
    End Function

    Public Sub New(ByVal connection As Connection)
        m_connection = connection
        Clear()
    End Sub

    Public Sub Clear()
        m_currentalbums = New Dictionary(Of String, Album)
        m_photos = Nothing
        m_currentalbum = Nothing
    End Sub

    ' read all rows from the Album table, create Album instances, add to dictionary

    Public Sub LoadAlbums()
        Dim rdr As SqlDataReader = Nothing
        Try
            m_currentalbums.Clear()

            ReportTrace("loading albums ...")
            Dim cmd As SqlCommand = m_connection.CreateCommand()
            cmd.CommandText = "SELECT * FROM " & Schema.Album
            ReportSqlCommand("loading album definitions", cmd)
            rdr = cmd.ExecuteReader()

            If Not rdr.HasRows Then
                ReportTrace("... not found")
            Else
                While rdr.Read()
                    ReportTrace(String.Format("album: {0} | {1} | {2}", rdr.Item(0), rdr.Item(1), rdr.Item(2)))
                    Dim rowguid As System.Guid = CType(rdr.Item(0), System.Guid)
                    Dim name As String = CType(rdr.Item(1), String)
                    Dim query As String = CType(rdr.Item(2), String)
                    Dim a As New Album(rowguid, name, query)
                    m_currentalbums.Add(name, a)
                    If m_first_album Is Nothing Then m_first_album = a
                End While
                ReportTrace("... read " & m_currentalbums.Count & " album(s)")
            End If
            rdr.Close()

            RaiseEvent AlbumsChanged()
            If m_currentalbum Is Nothing OrElse Not m_currentalbums.ContainsValue(m_currentalbum) Then LoadFirstAlbum()

        Catch ex As Exception
            ReportException(ex)
        Finally
            If rdr IsNot Nothing AndAlso Not rdr.IsClosed() Then rdr.Close()
        End Try

    End Sub

    Public Sub LoadFirstAlbum()
        m_currentalbum = m_first_album
        RaiseEvent CurrentAlbumChanged(m_currentalbum)
    End Sub

    Public Sub LoadAlbum()
        If False Then
            System.Threading.ThreadPool.QueueUserWorkItem(AddressOf WaitCallback)
        Else
            Dim thr As New System.Threading.Thread(AddressOf LoadAlbumInternal)
            thr.Name = "LoadAlbumThread"
            thr.Start()
        End If
    End Sub

    Private Sub WaitCallback(ByVal state As Object)
        LoadAlbumInternal()
    End Sub

    Private Sub LoadAlbumInternal()
        Try
            ReportTrace("loading photos for album '" & m_currentalbum.AlbumName & "' ...")

            Dim cmd As SqlCommand = m_connection.CreateCommand()
            cmd.CommandText = m_currentalbum.Query

            RaiseEvent StartAlbumLoad()
            m_photos = GetDataTable(Schema.Photo, cmd, m_connection)
            If m_photos Is Nothing Then Throw New ApplicationException("Cannot retrieve data from the " & Schema.Photo & " table. Check the SQL query.")
            RaiseEvent AlbumLoaded()
        Catch ex As Exception
            ReportException(ex)
        End Try

    End Sub

    Public Sub AddAlbum(ByVal a As Album)
        If m_currentalbums.ContainsValue(a) Then Return
        m_currentalbums.Add(a.AlbumName, a)
        Try
            Dim cmd As SqlCommand = m_connection.CreateCommand()
            cmd.CommandText = "INSERT INTO " & Schema.Album & " VALUES (@RowGuid, @Name, @Query)"
            cmd.Parameters.AddWithValue("@RowGuid", a.RowGuid)
            cmd.Parameters.AddWithValue("@Name", a.AlbumName)
            cmd.Parameters.AddWithValue("@Query", a.Query)
            ReportSqlCommand("storing album: " & a.AlbumName, cmd)
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            ReportException(ex)
        End Try

        RaiseEvent AlbumsChanged()
    End Sub

    Public Sub UpdateCurrentAlbum(ByVal query As String)
        Assert(m_currentalbum IsNot Nothing, "no current album")
        m_currentalbum.Query = query
        Try
            Dim cmd As SqlCommand = m_connection.CreateCommand()
            cmd.CommandText = "UPDATE " & Schema.Album & " SET Query = @Query WHERE RowGuid = @RowGuid"
            cmd.Parameters.AddWithValue("@RowGuid", m_currentalbum.RowGuid)
            cmd.Parameters.AddWithValue("@Query", query)
            ReportSqlCommand("updating album: " & m_currentalbum.AlbumName, cmd)
            cmd.ExecuteNonQuery()
            RaiseEvent CurrentAlbumChanged(m_currentalbum)
        Catch ex As Exception
            ReportException(ex)
        End Try
    End Sub

End Class
